package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.*;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.utils.spring.SpringBeanFactory;
import org.springframework.stereotype.Service;

/**
 * 标样核查服务
 * @author Administrator
 *
 */
@Service
public class StandardVerificationService {

	/**
	 * 查看标样核查记录，用于前端数据展示和报表导出
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getStandardVerificationList(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "  SELECT t1.*,to_char(t1.start_time,'iw') week_number,"
				+"round((decode(sign(t1.auto_result),null,0,-1,0,t1.auto_result)+decode(sign(t1.auto_result1),null,0,-1,0,t1.auto_result1)+decode(sign(t1.auto_result2),null,0,-1,0,t1.auto_result2))/( decode(sign(t1.auto_result),null,0,-1,0,1)+decode(sign(t1.auto_result1),null,0,-1,0,1)+decode(sign(t1.auto_result2),null,0,-1,0,1)),2)AVG,"
				+"round((((decode(sign(t1.auto_result),null,0,-1,0,t1.auto_result)+decode(sign(t1.auto_result1),null,0,-1,0,t1.auto_result1)+decode(sign(t1.auto_result2),null,0,-1,0,t1.auto_result2))/( decode(sign(t1.auto_result),null,0,-1,0,1)+decode(sign(t1.auto_result1),null,0,-1,0,1)+decode(sign(t1.auto_result2),null,0,-1,0,1))-t1.config_value)/t1.config_value)*100,2) percent,"
				+"t2.name as STATION_NAME,t4.C_USEREALNAME,t5.name as WTTP_NAME FROM OP_STAND_VERI t1 "
				+ "left join BC_SITE_INFO t2 on t1.station_id=t2.exp_no "
				+ " left join OPERATION_TASK t3 on t1.task_code=t3.task_code "
				+ " left join BC_ENTERPRISE_ATTR t5 on t2.wwtp_id=t5.exp_no "
				+ "left join safe_user t4 on t1.SUER_ID=t4.S_USERID WHERE 1=1 ";

		if (params.containsKey("task_code")) {
			sql += " and t1.TASK_CODE = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("wwtp_id")) {
				sql += " and t2.WWTP_ID = @wwtp_id";
				listParam.add(new GlobalParameter("wwtp_id", params.get("wwtp_id")));
			}

			if (params.containsKey("flow_type")) {
				sql += " and t2.flow_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			
			if (params.containsKey("water_type")) {
				sql += " and t2.STATION_CATEGORY = @water_type";
				listParam.add(new GlobalParameter("water_type", params.get("water_type")));
			}
			
			if (params.containsKey("station_id")) {
				sql += " and t1.station_id = @station_id";
				listParam.add(new GlobalParameter("station_id", params.get("station_id")));
			}

			if (params.containsKey("start")) {
				sql += " and t1.START_TIME >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and t1.START_TIME <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}

		sql = sql + " order by t1.START_TIME desc ";
		return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
	}
	
	/**
	 * 获取标样核查任务数据记录列表，用于有效性审核
	 * 
	 * @param params
	 * @return
	 */
	public List<RowBase> getStandardVerificationDataList(Map<String, String> params) {
		if (params == null)
			return null;
		
		String strSTATION_ID = params.get("STATION_ID");
		String strPARAM_ID = params.get("PARAM_ID");
		String strJOB_ID = params.get("JOB_ID");
		String strSTART_TIME = params.get("START_TIME");
		String strEND_TIME = params.get("END_TIME");
		
		if (StringUtil.IsNullOrEmpty(strSTATION_ID)
				|| StringUtil.IsNullOrEmpty(strPARAM_ID)
				|| StringUtil.IsNullOrEmpty(strJOB_ID))
			return null;

		String strSQL = " select t.TASK_CODE, t.TASK_STATUS, t.START_TIME as TASK_START_TIME, t.END_TIME as TASK_END_TIME, t.FINISH_TIME as TASK_FINISH_TIME,  \n"
				+ " t1.*, \n" + " t2.NAME as STATION_NAME, \n" + " t3.GROUP_NAME as GROUP_NAME, \n"
				+ " t4.C_USEREALNAME as USER_NAME \n" + " from OP_TASK t \n"
				+ " inner join OP_STAND_VERI t1 ON t1.TASK_CODE = t.TASK_CODE \n" // dif
				+ " left join BC_SITE_INFO t2 on t2.EXP_NO = t.STATION_ID \n"
				+ " left join OC_GROUP t3 on t3.GUID = t.OPERATION_GROUP_ID \n"
				+ " left join SAFE_USER t4 on t4.S_USERID = t.FINISH_BY \n" + " where t.STATION_ID = '" + strSTATION_ID
				+ "' \n" + " AND t.JOB_ID = '" + strJOB_ID + "' \n" + " AND t1.TEST_PROJECT = '" + strPARAM_ID + "' \n" // dif
				+ " AND t.START_TIME >= to_date('" + strSTART_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " AND t.START_TIME <= to_date('" + strEND_TIME + " 00:00:00', 'yyyy-mm-dd hh24:mi:ss') \n"
				+ " order by t.START_TIME desc ";

		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable("TASKQUERYDIC");
		List<RowBase> rcdList = DataUtil.getSelectRows(strSQL, null, pTable);
		return rcdList;
	}
	/**
	 * 统计标液核查仪器列表xwei
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getStandardVerifyDeviceList(Map<String, String> params) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<GlobalParameter>();
		String sql = "select distinct t1.Test_Project, T1.Device_Number,T3.S_DOMAINCAPTION TEST_PROJECT_DESC,t4.c_userealname\n" +
						"  from OP_STAND_VERI t1\n" + 
						"  left join data_defulatdomain t3 on t1.test_project = t3.s_domaincode\n" + 
						"                                 and t3.s_domainname = 'MONITOR_PARAM'"+
						"     left join safe_user t4 on t4.s_userid = t1.suer_id "+
						"where 1=1";

		if (params.containsKey("task_code")) {
			sql += " and t1.TASK_CODE = @task_code";
			listParam.add(new GlobalParameter("task_code", params.get("task_code")));
		} else {
			if (params.containsKey("flow_type")) {
				sql += " and t2.flow_type = @flow_type";
				listParam.add(new GlobalParameter("flow_type", params.get("flow_type")));
			}
			if (params.containsKey("start")) {
				sql += " and t1.START_TIME >= to_date('" + params.get("start") + " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			}
			if (params.containsKey("end")) {
				sql += " and t1.START_TIME <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			}
		}
		return DataUtil.getSelectRows(sql, listParam);
	}
}
